/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.marifnst.itd.util;

import com.marifnst.itd.preprocessing.Stemming;
import com.sun.rowset.WebRowSetImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.WebRowSet;

/**
 *
 * @author marifnst
 */
public class UtilDatabase {

    private static UtilDatabase utilDatabase;
    private Connection connection;
    private Statement statement;
    private WebRowSet webRowSet;

    public static UtilDatabase getInstance() {
        if (utilDatabase == null) {
            utilDatabase = new UtilDatabase();
        }
        return utilDatabase;
    }

    public void openConnection() {
        try {
            // windows
//            String url = "jdbc:hsqldb:file:/" + System.getProperty("user.dir").substring(3) + "/database/db_conf";
            // linux
            String url = "jdbc:hsqldb:file:/" + System.getProperty("user.dir") + "/database/db_conf";
            connection = DriverManager.getConnection(url, "sa", "");
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Stemming.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void closeConnection() {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(Stemming.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getSourceString(char key) {
        return "SET TABLE data_" + key + " SOURCE \"data_" + key + ".csv;ignore_first=true\"";
    }

    public String[][] executeQuery(char database, String query) {
        String[][] result = null;
        try {
            openConnection();
            statement.execute(getSourceString(database));

            webRowSet = new WebRowSetImpl();
            webRowSet.setCommand(query);
            webRowSet.execute(connection);

            result = new String[webRowSet.size()][3];
            int index = 0;
            while (webRowSet.next()) {
                result[index][0] = webRowSet.getString("id");
                result[index][1] = webRowSet.getString("name");
                result[index][2] = webRowSet.getString("dasar");
                index++;
//                System.out.println(webRowSet.getString("name"));
            }

            closeConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public boolean executeUpdate(char database, String query) {
        boolean result = false;
        try {
            openConnection();
            statement.execute(getSourceString(database));

            webRowSet = new WebRowSetImpl();
            webRowSet.setCommand(query);
            webRowSet.execute(connection);

            closeConnection();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);            
        }
        return result;
    }

    public static void main(String args[]) {
        UtilDatabase.getInstance().executeQuery('a', "select * from data_a");
    }
}
